#!/usr/bin/env ruby
# -*- coding: utf-8 -*-
require 'spreadsheet'

# Controled random generator in order to avoid redundance in the test.
class CustomRandom
  def initialize(nb_lines)
    @random_generator = Random.new
    @last = 0

    nb_per_columns = nb_lines / 4
    @remaining = Array.new(4, nb_per_columns)
    (nb_lines - nb_per_columns * 4).times do |i|  
      @remaining[@random_generator.rand(4)] += 1
    end
  end

  def next
    all_null = true
    @remaining.each do |value|
      if value != 0
        all_null = false
        break
      end
    end
    if all_null
      return @random_generator.rand(4)
    else
      i = 0
      i = @random_generator.rand(4) while (i == @last and @remaining.inject(:+) > @remaining[@last]) or @remaining[i] == 0
      @remaining[i] -= 1
      @last = i
      return i
    end
  end
end

# Verb struct declaration
Verb = Struct.new(:verbalbase, :preterit, :pastparticiples, :traduction)

# Custum exceptions
class UnvalidVerbException < Exception
end

# Return an array of the sheet's names in the given xls document
def getSheetsName(spreadsheet_path)
  Spreadsheet.client_encoding = 'UTF-8'
  begin
    book = Spreadsheet.open spreadsheet_path
    sheets_name = []
    book.worksheets.each {|sheet| sheets_name.push(sheet.name)}
    return sheets_name
  rescue Ole::Storage::FormatError
    puts "ERROR> The given file isn't a valid xls file !"
    raise
  end
end

# Return a dictionary of all the verbs available in the given xls sheet,
# indexed with the verbal base of the verb.
def getVerbs(spreadsheet_path, sheet_name)
  Spreadsheet.client_encoding = 'UTF-8'
  begin
    book = Spreadsheet.open spreadsheet_path
    sheet = book.worksheet sheet_name

    # Check that the given sheet exists
    if sheet.nil?
      puts "The given sheet '%s' doesn't exist in the xls"
      raise
    end

    verbs = {}
    sheet.each do |row|
      # Get cell informations for the current raw
      verbalbase = row[0]
      preterit = row[1]
      pastparticiples = row[2]
      traduction = row[3]

      # Check that for this raw all the information have been filled
      if verbalbase == nil or 
          preterit == nil or
          pastparticiples == nil or
          traduction == nil then
        raise UnvalidVerbException, "non valid verb found in file ! (%s, %s, %s, %s)" % [verbalbase, preterit, pastparticiples, traduction]
      end

      # Push a new verb in the list
      verbs[verbalbase] = Verb.new(verbalbase, preterit, pastparticiples, traduction)
    end

    return verbs

  rescue Ole::Storage::FormatError
    puts "ERROR> The given file isn't a valid xls file !"
    raise
  end
end

def generateTest(verbs, from, to, nblines)

  # First sort alphabeticaly the verbs and get the first and the last one
  sorted_array = verbs.sort
  verbs = Hash[sorted_array]

  # Check that from and to are both in the verb list
  if from.nil? or verbs[from].nil?
    raise "The verb '%s' hasn't been found in the xls !" % from
  end
  if not to.nil? and verbs[to].nil?
    raise "The verb '%s' hasn't been found in the xls !" % to
  end

  # Remove all the verbs from the list which are not included between from and to
  verbs_array = verbs.to_a
  verbs_array.select! {|item| item[0].casecmp(from) >=0 and item[0].casecmp(to) <=0}

  # Check that we have enough verbs to generate the required number of lines in the test
  if verbs_array.size < nblines
    raise "The selected range of verbs is too short to have %d lines !" % nblines
  end

  random = CustomRandom.new(nblines)
  test = []
  solutions = []  
  
  # Shuffle the verb list to break the alphabetic order
  verbs_array.shuffle!

  # Create test and solutions list
  nblines.times do |i|
    # Take a random verb in the verbs array
    verb = verbs_array[i][1]
    # Sample another random number to determine which column we want to fill for this line
    j = random.next
    # Add the column index and the value to the test array
    test.push([j, verb[j]])
    # Push the other verb forms in the solution array
    verb.size.times {|index| solutions.push(verb[index]) if index != j}
  end

  solutions.shuffle!

  return test, solutions
end

def exportTestToXls(test, solutions, export_path)
  
  # create all the formats
  header_font = Spreadsheet::Font.new("Comic Sans MS")
  header_font.bold = true
  header_font.size = 11
  header_format = Spreadsheet::Format.new :align => :center, :border => true, :font => header_font

  common_font = Spreadsheet::Font.new("Comic Sans MS")
  common_font.bold = false
  common_font.size = 11
  common_format = Spreadsheet::Format.new :align => :center, :border => true, :font => common_font

  solutions_format = Spreadsheet::Format.new :align => :merge, :font => common_font

  # Create a new spreadsheet
  book = Spreadsheet::Workbook.new
  sheet = book.create_worksheet

  # Apply formating to the sheet
  sheet.row(0).concat ["Base verbale", "Prétérit", "Participe passé", "Traduction", "Points"]
  5.times {|i| sheet.row(0).set_format(i, header_format)}
  test.size.times do |i|
    5.times {|j| sheet.row(i+1).set_format(j, common_format)}
    sheet.row(i+1).height += 5
  end

  # Fill spreadsheet with the test content
  test.each_index {|index| sheet[index+1,test[index][0]] = test[index][1]}

  # Adapt cell size depending on the content size.
  max_char = 0
  4.times do |i|
    test.size.times do |j|
      max_char = sheet[j,i].size if not sheet[j,i].nil? and sheet[j,i].size > max_char
    end
  end
  4.times {|i| sheet.column(i).width = max_char+5}

  # Put the solutions at the end of the test if we have any
  if not solutions.nil?
    solutions.each_index do |i|
      row_index = test.size+1+i/8 # We put 8 solutions per row.
      # If the row hasn't been created yet, we create it.
      if sheet[row_index,0].nil?
        sheet[row_index,0] = ""
        5.times {|i| sheet.row(row_index).set_format(i, solutions_format)}
      end

      sheet[row_index,0] << solutions[i] << " / "
    end
  end

  book.write export_path
end

  

